package qldoanra.thongkesoluong;

import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.faces.model.SelectItem;
import dbutil.ConnectDB;
import utils.DateUtils;
import utils.UtilsOnScopes;

public class BeanSoLuong {
	private String error;
	private java.util.Date starDate;
	private java.util.Date endDate;
	private String nationTo;
	private String alert;
	private String dinhky;
	private String nam;
	private String disble;
	private boolean tableData;
	private String bieumau="/qldoanra/thongkesonguoidi/emptyBieuMau.xhtml";
	private List<TableDisplay> list; 
	
	private List<Nation> getNations() {
		List<Nation> list = new ArrayList<Nation>();
		String SQL = "SELECT DISTINCT nuocden from doanra";
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB
				.getDataTypeList(SQL);
		try {
			if (array.size() > 0) {
				HashMap<String, Object> hash = new HashMap<String, Object>();
				for (int i = 0; i < array.size(); i++) {
					Nation nation = new Nation();
					hash = (HashMap<String, Object>) array.get(i);
					nation.setValue(hash.get("nuocden") == null ? "" : hash
							.get("nuocden").toString());
					nation.setLable(hash.get("nuocden") == null ? "" : hash
							.get("nuocden").toString());
					list.add(nation);
				}
				ConnectDB.connect.close();
			}
		} catch (Exception e) {

		}
		return list;
	}

	public SelectItem[] listItemNation() {
		List<Nation> li = this.getNations();
		SelectItem[] arrItem = new SelectItem[li.size() + 1];
		arrItem[0] = new SelectItem("-1", "---Chọn---");
		for (int i = 0; i < li.size(); i++) {
			Nation nation = li.get(i);
			arrItem[i + 1] = new SelectItem(nation.getValue(), nation
					.getLable());
		}
		return arrItem;
	}

	public SelectItem[] getListItemNation() {
		return this.listItemNation();
	}
	public String setEnable(){
		String result= "true";
		return result;
	}
	
	public String view(){
		this.setError("");
		String total ="0";
		if(this.checkValue(this.starDate, this.endDate) == false){
			return "";
		}else if(this.nationTo == null || this.nationTo.equals("-1")){
			this.setError("Bạn chưa nhập nước đến !");
			this.setAlert("");
			return "";
		}else {
			this.tables();
			total = String.valueOf(this.getList().size());
			if(this.tableData == true){
				this.setBieumau("/qldoanra/thongkesonguoidi/bieumau.xhtml");
			}
			else{
				this.setBieumau("/qldoanra/thongkesonguoidi/emptyBieuMau.xhtml");
			}
		}
//		String dkDinhky = this.filtData(this.nam, this.dinhky);
//		String SQL = "SELECT COUNT(*) AS count FROM doanra D " +
//				" INNER JOIN dsnguoidi B ON D.madoan = B.iddoanra" +
//				" WHERE B.trangthaidi = true AND D.nuocden LIKE '" +this.nationTo+"' "
//				+ " AND D.flagdelete = 0 "
//				+ dkDinhky;
//		ArrayList<HashMap<String, Object>> array = ConnectDB
//		.getDataTypeList(SQL);
//		try {
//			if (array.size() > 0) {
//				HashMap<String, Object> hash = new HashMap<String, Object>();
//				hash = (HashMap<String, Object>) array.get(0);
//				total = hash.get("count").toString();
//				if(this.tableData == true){
//					this.tables();
//					if(this.list.size() > 0){
//					this.setBieumau("/qldoanra/thongkesonguoidi/bieumau.xhtml");
//					}else{
//						this.setBieumau("/qldoanra/thongkesonguoidi/emptyBieuMau.xhtml");
//					}
//				}else{
//					this.setBieumau("/qldoanra/thongkesonguoidi/emptyBieuMau.xhtml");
//				}
//			}
//		}catch(Exception e){
//			
//		}
//		System.out.println(SQL);
		this.setAlert("Tổng số lượt người đi đến <b>"+ this.nationTo +"</b> <br/>" +
				" từ ngày:"+DateUtils.formatDateUtilToString(this.starDate) +" đến ngày:"+
				DateUtils.formatDateUtilToString(this.endDate)+ " là:<b> "+total +"</b> lượt người");
		return "okThongKeSoLuong";
	}
	
	private String filtData(String nam, String dky) {
		String result = "";
		if (this.starDate != null && this.endDate != null) {
			Date starDateSQL = DateUtils.utilDateToSqlDate(this.getStarDate());
			Date endDateSQL = DateUtils.utilDateToSqlDate(this.getEndDate());
			result = " AND D.ngaydi BETWEEN '" + starDateSQL + "' AND '"
					+ endDateSQL + "'";
		}
		return result;
	}
	
	/**************************************
	 * Method name		: checkValue
	 * Return type		: boolean
	 * Decription		:Kiem tra du lieu dau vao
	 * Created date		: Jun 18, 2008
	 * Author			: Honglk
	 **************************************/
	public boolean checkValue( java.util.Date star,java.util.Date  end){
		boolean result = true;
		if(star == null || star.equals("")){
			this.setError("Bạn chưa chọn ngày bắt đầu !");
			return result = false;
		}
		else if(end == null || end.equals("")){
			this.setError("Bạn chưa chọn ngày kết thúc !");
		  return result = false;
		}
		else if(star.compareTo(end) > 0){
			this.setError("Ngày kết thúc lớn hơn ngày bắt đầu !");
			return result = false;
			
		}
		return result;
	}
	
	
	public String tables() {
		List<TableDisplay> list = new ArrayList<TableDisplay>();
		String dkDinhky = this.filtData(this.nam, this.dinhky);
		String SQL = "select DISTINCT * " +
					  " from hochieu A inner join canhan B on A.cmnd = B.cmnd "+
		              " inner join dsnguoidi C on C.sohc = A.sohc " +
		              " inner join doanra D on D.madoan =C.iddoanra " +
		              " inner join mucdich E on D.idmucdich = E.id " +
		              " inner join quyetdinh F on D.idquyetdinh = F.id " +
		              " inner join (select madoan,sohc,max(G.ngaythaotac) as ngaytrathucte,min(G.ngaythaotac) as ngaynhanhc"
		              +             " from nhatkytranhanhc G group by G.madoan,G.sohc) K "+
		              " on (a.sohc = K.sohc and K.madoan = d.madoan)" +
		              " WHERE "  
		              + " D.nuocden LIKE '"+this.nationTo +"' "
		              + " AND C.trangthaidi=true AND D.flagdelete = 0 "
		              + dkDinhky ;
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB
				.getDataTypeList(SQL);
		try {
			if (array.size() > 0) {
				HashMap<String, Object> hash = new HashMap<String, Object>();
				int stt = 0;
				for (int i = 0; i < array.size(); i++) {
					stt = i + 1;
					hash = (HashMap<String, Object>) array.get(i);
					String ngayve = DateUtils.formatDate(hash.get("ngayve")== null?"":hash.get("ngayve").toString());
					TableDisplay table = new TableDisplay();
					table.setStt(String.valueOf(stt));
					table.setChiphi(hash.get("chiphi")==null?"":hash.get("chiphi").toString());
					table.setChucvu(hash.get("chucvu")== null?"":hash.get("chucvu").toString());
					table.setCmnd(hash.get("cmnd")== null?"":hash.get("cmnd").toString());
					table.setCoquan(hash.get("coquan")==null?"":hash.get("coquan").toString());
					table.setDonvimoi(hash.get("donvimoi")==null?"":hash.get("donvimoi").toString());
					table.setGioitinh(hash.get("gioitinh")==null?"":hash.get("gioitinh").toString());
					table.setLinhvuc(hash.get("linhvuc")==null?"":hash.get("linhvuc").toString());
					table.setLoaidoan(hash.get("loaidoan")==null?"":hash.get("loaidoan").toString());
					table.setMucdich(hash.get("mucdich")==null?"":hash.get("mucdich").toString());
					table.setNamsinh(hash.get("namsinh")==null?"":hash.get("namsinh").toString());
					table.setNgaydi(DateUtils.formatDate(hash.get("ngaydi")==null?"":hash.get("ngaydi").toString()));
					table.setNgayve(ngayve);
					String ngaytrathucte = DateUtils.formatDate(hash.get("ngaytrathucte")==null?"":hash.get("ngaytrathucte").toString());
					String ngaygiao = DateUtils.formatDate(hash.get("ngaynhanhc")==null?"":hash.get("ngaynhanhc").toString());
					if(ngaytrathucte.equalsIgnoreCase(ngaygiao)){
						table.setNgaygiaohc(ngaygiao);
						table.setTrangthainop("Chưa nộp");
						table.setNgaynopthucte("");
					}else{
						table.setTrangthainop("Đã nộp");
						table.setNgaygiaohc(ngaygiao);
						table.setNgaynopthucte(ngaytrathucte);
					}
					table.setNgaykyqd(DateUtils.formatDate(hash.get("ngaykyqd")== null?"":hash.get("ngaykyqd").toString()));
					String ngayquidinhnop = DateUtils.CongTruNgay(ngayve,7);
					table.setNgayquydinhnop(ngayquidinhnop);
					
					table.setNguoikyqd(hash.get("nguoikyqd")==null?"":hash.get("nguoikyqd").toString());
					table.setNuocden(hash.get("nuocden")==null?"":hash.get("nuocden").toString());
					table.setSohc(hash.get("sohc")==null?"":hash.get("sohc").toString());
					table.setSoqd(hash.get("soqd")== null?"":hash.get("soqd").toString());
					table.setTen(hash.get("ten")==null?"":hash.get("ten").toString());
					table.setTrangthai(hash.get("trangthai")==null?"":hash.get("trangthai").toString());
					table.setTrangthaidi(hash.get("trangthaidi")==null?"":hash.get("trangthaidi").toString());
					table.setMadoan(hash.get("madoan")==null?"":hash.get("madoan").toString());
					if(hash.get("dangvien")!=null &&hash.get("dangvien").toString().equals("true")){
						table.setPathImgDangVien("/qldoanra/img/Ok.gif");
					}
					if(hash.get("baocaokq")!=null &&hash.get("baocaokq").toString().equals("true")){
						table.setPathImgBaoCaoKQ("/qldoanra/img/Ok.gif");
						table.setBaocaokq("Có");
					}
					else if (hash.get("baocaokq").toString().equals("false")){
						table.setBaocaokq("Không");
					}
					table.setLoaihc(hash.get("loaihc")==null?"":hash.get("loaihc").toString());
					list.add(table);
//					this.setBieumau("/qldoanra/baocaodinhky/bieumau.xhtml");
				}
			}else{
				list = new ArrayList<TableDisplay>();
			}
			this.setList(list);
			//ConnectDB.connect.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	
	public String getDinhky() {
		return dinhky;
	}
	
	public String getDisble() {
		return disble;
	}

	public void setDisble(String disble) {
		this.disble = disble;
	}

	public String getNam() {
		return nam;
	}

	public void setNam(String nam) {
		this.nam = nam;
	}

	public void setDinhky(String dinhky) {
		this.dinhky = dinhky;
	}

	public String reset() {
		UtilsOnScopes.getSession().removeAttribute("beanSoLuong");
		return "okThongKeSoLuong";
	}

	public String getError() {
		return error;
	}

	public void setError(String error) {
		this.error = error;
	}

	public java.util.Date getStarDate() {
		return starDate;
	}

	public void setStarDate(java.util.Date starDate) {
		this.starDate = starDate;
	}

	public java.util.Date getEndDate() {
		return endDate;
	}

	public void setEndDate(java.util.Date endDate) {
		this.endDate = endDate;
	}

	public String getNationTo() {
		return nationTo;
	}

	public void setNationTo(String nationTo) {
		this.nationTo = nationTo;
	}

	public String getAlert() {
		return alert;
	}

	public void setAlert(String alert) {
		this.alert = alert;
	}

	public boolean isTableData() {
		return tableData;
	}

	public void setTableData(boolean tableData) {
		this.tableData = tableData;
	}

	public String getBieumau() {
		return bieumau;
	}

	public void setBieumau(String bieumau) {
		this.bieumau = bieumau;
	}

	public List<TableDisplay> getList() {
		return list;
	}

	public void setList(List<TableDisplay> list) {
		this.list = list;
	}
	

}
